#!/usr/bin/env python 
# -*- coding: utf-8 -*-
import csv
import os
from collections import defaultdict

import pandas as pd
import xlrd

CSV_ENCODING = 'utf-8'
DATA_DIRECTORY = 'data'
OVERVIEW = '概述.csv'
NATION = '民族.csv'
SEX = '性别.csv'
TITLE = '职称.csv'
EDUCATION = '学历.csv'
YOUTH = '35岁青年占比.csv'

NAME_PROVINCE_MAP = {'刘晓伟': 'liaoning03', '房佳': 'tianjin03', '王镜凯': 'guizhou03', '范克岩': 'shandong03',
                     '赵越': 'jilin03', '郝修海': 'lanzhou03', '闫志宏': 'neimenggu03', '陈瑜珊': 'fujian03'}
EN_CN = {
    'anhui03': '安徽',
    'beijing03': '北京',
    'bingtuan03': '兵团',
    'chongqing03': '重庆',
    'guangdong03': '广东',
    'guangxi03': '广西',
    'hainan03': '海南',
    'hebei03': '河北',
    'heilongjiang03': '黑龙江',
    'henan03': '河南',
    'hubei03': '湖北',
    'hunan03': '湖南',
    'jiangsu03': '江苏',
    'jiangxi03': '江西',
    'jin03': '山西',
    'ningxia03': '宁夏',
    'qinghai03': '青海',
    'shanghai03': '上海',
    'shanxi03': '陕西',
    'sichuan03': '四川',
    'xinjiang03': '新疆',
    'xizang03': '西藏',
    'yunnan03': '云南',
    'liaoning03': '辽宁',
    'tianjin03': '天津',
    'guizhou03': '贵州',
    'jilin03': '吉林',
    'lanzhou03': '兰州',
    'neimenggu03': '内蒙古', 'zhejiang03': '浙江', 'shandong03': '山东', 'fujian03': '福建', 'gansu03': '甘肃'
}

jiaozhigong_zongshu_c = 3  # 教职工总数D
jiaozhigong_dangyuan_zongshu_c = 4  # 教职工党员总数E
zhuanrenjiaoshi_zongshu_c = 5  # 专任教师总数F
sanshiwusui_yixia_c = 6
zhuanrenjiaoshi_dangyuan_zongshu_C = 7  # 专任教师党员总数H

shenqingrudang_c = 12
rudang_jijifenzi = 13
dangnian_fazhan_dangyuan = 9

zhenggao_c = 15
fugao_c = 16
qita_zhicheng_c = 17

boshi_c = 18
shuoshi_c = 19
qita_xueli_c = 20

zongji_r = 6  # search by '总计'
shaoshuminzu_r = 7
zhuangzu_r = 8
huizu_r = 9
weiwuerzu_r = 10
zangzu_r = 11
mengguzu_r = 12
nvxing_r = 13


class SchoolInfo():
    def __init__(self):
        self.name = ''  # for debug
        self.jiaozhigong_zongshu = 0  # 31
        self.jiaozhigong_dangyuan_zongshu = 0  # 32
        self.zhuanrenjiaoshi_zongshu = 0  # 33
        self.zhuanrenjiaoshi_dangyuan_zongshu = 0  # 34

        self.zhuanrenjiaoshi_dangyuanzongshu_shaoshuminzu = 0  # 35
        self.zhuanrenjiaoshi_dangyuanzongshu_zhuang = 0  # 36
        self.zhuanrenjiaoshi_dangyuanzongshu_hui = 0  # 37
        self.zhuanrenjiaoshi_dangyuanzongshu_weiwuer = 0  # 38
        self.zhuanrenjiaoshi_dangyuanzongshu_zang = 0  # 39
        self.zhuanrenjiaoshi_dangyuanzongshu_menggu = 0  # 40

        self.zhuanrenjiaoshi_dangyuan_nvxing = 0  # 41
        self.zhuanrenjiaoshi_dangyuan_nanxing = 0  # 42

        self.zhuanrenjiaoshi_dangyuan_zhenggao = 0  # 43
        self.zhuanrenjiaoshi_dangyuan_fugao = 0  # 44
        self.zhuanrenjiaoshi_dangyuan_qitazhicheng = 0  # 44

        self.zhuanrenjiaoshi_dangyuan_boshi = 0  # 45
        self.zhuanrenjiaoshi_dangyuan_shuoshi = 0  # 46
        self.zhuanrenjiaoshi_dangyuan_qitaxueli = 0  # 47

        self.shenqingrudang_jiaoshi_shuliang = 0  # 48
        self.rudang_jijifenzi = 0  # 49
        self.dangnian_fazhan_dangyuan_shuliang = 0  # 50

        self.sanshiwusuiyixia_qingnianjiaoshi_zongshu = 0  # 51
        self.sanshiwusuiyixia_qingnianjiaoshi_zhanbi = 0  # 53


def list_xls_by_province():
    province_files = defaultdict(list)
    for root, dirs, files in os.walk(DATA_DIRECTORY):
        for name in files:
            relpath = os.path.normpath(os.path.join(root, name))
            _, year, province, school, name = relpath.split(os.sep)
            province = NAME_PROVINCE_MAP.get(province, province)  # fix chinese name to province pinyin
            if year.startswith('2018') or year.startswith('2019'):  # 2018/2019 use attachment4
                if not name.startswith('附件4'):
                    continue
            if year.startswith('2020') or '2021' in year:  # 2020/2021 use attachment5
                if not name.startswith('附件5'):
                    continue
            province_files[province].append(relpath)
    return province_files


def read_xls(d: dict):
    dd = defaultdict(list)
    for k, v in d.items():
        for xls in v:
            book = xlrd.open_workbook(xls)
            sh = book.sheet_by_index(0)
            s = SchoolInfo()
            s.name = xls
            s.jiaozhigong_zongshu = sh.cell_value(zongji_r, jiaozhigong_zongshu_c)
            s.jiaozhigong_dangyuan_zongshu = sh.cell_value(zongji_r, jiaozhigong_dangyuan_zongshu_c)
            s.zhuanrenjiaoshi_zongshu = sh.cell_value(zongji_r, zhuanrenjiaoshi_zongshu_c)
            s.zhuanrenjiaoshi_dangyuan_zongshu = sh.cell_value(zongji_r, zhuanrenjiaoshi_dangyuan_zongshu_C)

            s.zhuanrenjiaoshi_dangyuanzongshu_shaoshuminzu = sh.cell_value(shaoshuminzu_r,
                                                                           zhuanrenjiaoshi_dangyuan_zongshu_C)
            s.zhuanrenjiaoshi_dangyuanzongshu_zhuang = sh.cell_value(zhuangzu_r, zhuanrenjiaoshi_dangyuan_zongshu_C)
            s.zhuanrenjiaoshi_dangyuanzongshu_hui = sh.cell_value(huizu_r, zhuanrenjiaoshi_dangyuan_zongshu_C)
            s.zhuanrenjiaoshi_dangyuanzongshu_weiwuer = sh.cell_value(weiwuerzu_r, zhuanrenjiaoshi_dangyuan_zongshu_C)
            s.zhuanrenjiaoshi_dangyuanzongshu_zang = sh.cell_value(zangzu_r, zhuanrenjiaoshi_dangyuan_zongshu_C)
            s.zhuanrenjiaoshi_dangyuanzongshu_menggu = sh.cell_value(mengguzu_r, zhuanrenjiaoshi_dangyuan_zongshu_C)

            s.zhuanrenjiaoshi_dangyuan_nvxing = sh.cell_value(nvxing_r, zhuanrenjiaoshi_dangyuan_zongshu_C)

            s.zhuanrenjiaoshi_dangyuan_zhenggao = sh.cell_value(zongji_r, zhenggao_c)
            s.zhuanrenjiaoshi_dangyuan_fugao = sh.cell_value(zongji_r, fugao_c)
            s.zhuanrenjiaoshi_dangyuan_qitazhicheng = sh.cell_value(zongji_r, qita_zhicheng_c)

            s.zhuanrenjiaoshi_dangyuan_boshi = sh.cell_value(zongji_r, boshi_c)
            s.zhuanrenjiaoshi_dangyuan_shuoshi = sh.cell_value(zongji_r, shuoshi_c)
            s.zhuanrenjiaoshi_dangyuan_qitaxueli = sh.cell_value(zongji_r, qita_xueli_c)

            s.shenqingrudang_jiaoshi_shuliang = sh.cell_value(zongji_r, shenqingrudang_c)
            s.rudang_jijifenzi = sh.cell_value(zongji_r, rudang_jijifenzi)
            s.dangnian_fazhan_dangyuan_shuliang = sh.cell_value(zongji_r, dangnian_fazhan_dangyuan)

            s.sanshiwusuiyixia_qingnianjiaoshi_zongshu = sh.cell_value(zongji_r, sanshiwusui_yixia_c)

            dd[k].append(s)
    return dd


def calc(dd):
    ddd = {}
    for k, v in dd.items():
        totals = SchoolInfo()
        for s in v:
            totals.jiaozhigong_zongshu += num(s.jiaozhigong_zongshu)
            totals.jiaozhigong_dangyuan_zongshu += num(s.jiaozhigong_dangyuan_zongshu)
            totals.zhuanrenjiaoshi_zongshu += num(s.zhuanrenjiaoshi_zongshu)
            totals.zhuanrenjiaoshi_dangyuan_zongshu += num(s.zhuanrenjiaoshi_dangyuan_zongshu)

            totals.zhuanrenjiaoshi_dangyuanzongshu_shaoshuminzu += num(s.zhuanrenjiaoshi_dangyuanzongshu_shaoshuminzu)
            totals.zhuanrenjiaoshi_dangyuanzongshu_zhuang += num(s.zhuanrenjiaoshi_dangyuanzongshu_zhuang)
            totals.zhuanrenjiaoshi_dangyuanzongshu_hui += num(s.zhuanrenjiaoshi_dangyuanzongshu_hui)
            totals.zhuanrenjiaoshi_dangyuanzongshu_weiwuer += num(s.zhuanrenjiaoshi_dangyuanzongshu_weiwuer)
            totals.zhuanrenjiaoshi_dangyuanzongshu_zang += num(s.zhuanrenjiaoshi_dangyuanzongshu_zang)
            totals.zhuanrenjiaoshi_dangyuanzongshu_menggu += num(s.zhuanrenjiaoshi_dangyuanzongshu_menggu)

            totals.zhuanrenjiaoshi_dangyuan_nvxing += num(s.zhuanrenjiaoshi_dangyuan_nvxing)
            totals.zhuanrenjiaoshi_dangyuan_nanxing += num(s.zhuanrenjiaoshi_zongshu) - num(
                s.zhuanrenjiaoshi_dangyuan_nvxing)

            totals.zhuanrenjiaoshi_dangyuan_zhenggao += num(s.zhuanrenjiaoshi_dangyuan_zhenggao)
            totals.zhuanrenjiaoshi_dangyuan_fugao += num(s.zhuanrenjiaoshi_dangyuan_fugao)
            totals.zhuanrenjiaoshi_dangyuan_qitazhicheng += num(s.zhuanrenjiaoshi_dangyuan_qitazhicheng)

            totals.zhuanrenjiaoshi_dangyuan_boshi += num(s.zhuanrenjiaoshi_dangyuan_boshi)
            totals.zhuanrenjiaoshi_dangyuan_shuoshi += num(s.zhuanrenjiaoshi_dangyuan_shuoshi)
            totals.zhuanrenjiaoshi_dangyuan_qitaxueli += num(s.zhuanrenjiaoshi_dangyuan_qitaxueli)

            totals.shenqingrudang_jiaoshi_shuliang += num(s.shenqingrudang_jiaoshi_shuliang)
            totals.rudang_jijifenzi += num(s.rudang_jijifenzi)
            totals.dangnian_fazhan_dangyuan_shuliang += num(s.dangnian_fazhan_dangyuan_shuliang)

            totals.sanshiwusuiyixia_qingnianjiaoshi_zongshu += num(s.sanshiwusuiyixia_qingnianjiaoshi_zongshu)
        totals.sanshiwusuiyixia_qingnianjiaoshi_zhanbi += totals.sanshiwusuiyixia_qingnianjiaoshi_zongshu / totals.zhuanrenjiaoshi_zongshu
        cn_province = EN_CN.get(k, k)
        ddd[cn_province] = totals
    return ddd


def show_csv(csv_path):
    title = csv_path[0:csv_path.index('.')]
    print(title)
    df = pd.read_csv(csv_path)
    print(df)
    print()


def write_csvs(ddd: dict):
    with open(OVERVIEW, 'w', newline='', encoding=CSV_ENCODING) as f:
        writer = csv.writer(f)
        writer.writerow(['省份', '教职工总数', '教职工党员总数', '专任教师总数', '专任教师党员数'])
        for k, totals in ddd.items():
            r = [k, totals.jiaozhigong_zongshu, totals.jiaozhigong_dangyuan_zongshu, totals.zhuanrenjiaoshi_zongshu,
                 totals.zhuanrenjiaoshi_dangyuan_zongshu]
            writer.writerow(r)
    show_csv(OVERVIEW)

    with open(NATION, 'w', newline='', encoding=CSV_ENCODING) as f:
        writer = csv.writer(f)
        writer.writerow(['省份', '少数民族', '壮', '回', '维吾尔', '藏', '蒙古'])
        for k, totals in ddd.items():
            r = [k, totals.zhuanrenjiaoshi_dangyuanzongshu_shaoshuminzu, totals.zhuanrenjiaoshi_dangyuanzongshu_zhuang,
                 totals.zhuanrenjiaoshi_dangyuanzongshu_hui, totals.zhuanrenjiaoshi_dangyuanzongshu_weiwuer,
                 totals.zhuanrenjiaoshi_dangyuanzongshu_zang, totals.zhuanrenjiaoshi_dangyuanzongshu_menggu
                 ]
            writer.writerow(r)
    show_csv(NATION)

    with open(SEX, 'w', newline='', encoding=CSV_ENCODING) as f:
        writer = csv.writer(f)
        writer.writerow(['省份', '男', '女'])
        for k, totals in ddd.items():
            r = [k, totals.zhuanrenjiaoshi_dangyuan_nanxing, totals.zhuanrenjiaoshi_dangyuan_nvxing]
            writer.writerow(r)
    show_csv(SEX)

    with open(TITLE, 'w', newline='', encoding=CSV_ENCODING) as f:
        writer = csv.writer(f)
        writer.writerow(['省份', '正高', '副高', '其他职称'])
        for k, totals in ddd.items():
            r = [k, totals.zhuanrenjiaoshi_dangyuan_zhenggao, totals.zhuanrenjiaoshi_dangyuan_fugao,
                 totals.zhuanrenjiaoshi_dangyuan_qitazhicheng]
            writer.writerow(r)
    show_csv(TITLE)

    with open(EDUCATION, 'w', newline='', encoding=CSV_ENCODING) as f:
        writer = csv.writer(f)
        writer.writerow(['省份', '博士', '硕士', '其他学历'])
        for k, totals in ddd.items():
            r = [k, totals.zhuanrenjiaoshi_dangyuan_boshi, totals.zhuanrenjiaoshi_dangyuan_shuoshi,
                 totals.zhuanrenjiaoshi_dangyuan_qitaxueli]
            writer.writerow(r)
    show_csv(EDUCATION)

    with open(YOUTH, 'w', newline='', encoding=CSV_ENCODING) as f:
        writer = csv.writer(f)
        writer.writerow(['省份', '35岁以下', '专任教师总数', '青年教师占比'])
        for k, totals in ddd.items():
            r = [k, totals.sanshiwusuiyixia_qingnianjiaoshi_zongshu, totals.zhuanrenjiaoshi_zongshu,
                 totals.sanshiwusuiyixia_qingnianjiaoshi_zhanbi]
            writer.writerow(r)
    show_csv(YOUTH)


def num(s):
    return int(0 if s == '' else int(s))


if __name__ == '__main__':
    d = list_xls_by_province()
    dd = read_xls(d)
    ddd = calc(dd)
    write_csvs(ddd)
